﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SQLite;

namespace SimpleOrmCFPC.Apis.Db.Sqlite
{
    public class SqLiteDb : IDb
    {
        private SQLiteConnection cnn;

        public SqLiteDb(SQLiteConnection cnn)
        {
            this.cnn = cnn;
        }

        public ISQLReader Execute(string sqlRequest, List<object> parameters)
        {
            using (DbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = sqlRequest;

                foreach (object value in parameters)
                {
                    DbParameter Field1 = cmd.CreateParameter();
                    Field1.Value = value;
                    cmd.Parameters.Add(Field1);
                }
                DbDataReader reader = cmd.ExecuteReader();
                return new SQLiteReader(reader);
            }
        }

        public void ExecuteNonQuery(string s)
        {
            using (DbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = s;
                cmd.ExecuteNonQuery();
            }
        }

        public object ExecuteNonQuery(string insertSql, IEnumerable<object> values)
        {
            using (DbCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = insertSql;

                foreach (object value in values)
                {
                    DbParameter Field1 = cmd.CreateParameter();
                    Field1.Value = value;
                    cmd.Parameters.Add(Field1);
                }
                cmd.ExecuteNonQuery();
            }
            SQLiteCommand cmd2 = cnn.CreateCommand();
            cmd2.CommandText = "SELECT last_insert_rowid()";
            return cmd2.ExecuteScalar();
        }

        public T ExecuteQuery<T>(string query)
        {
            var command = cnn.CreateCommand();
            command.CommandText = query;
            return (T)Convert.ChangeType(command.ExecuteScalar(), typeof(T), null);
        }
    }
}